package database;

import java.sql.*;
import java.util.*;

import com.mysql.jdbc.PreparedStatement;

import objects.*;

public class DBHandler {

	private static final String DB_URL = "jdbc:mysql://localhost/pathfinder"; 

	private static final String USER = "root"; 
	private static final String PASS = "P4thf1nd3r"; 


	//hier kommen die getListen $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

	public static List<Char> getChar(String name){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Char> charList = new ArrayList<Char>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `character` WHERE name = '"+name+"'");

			while (rs.next()) {
				charList.add(mapChar(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return charList;
	}

	public static List<Char> getCharByID(int id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Char> charList = new ArrayList<Char>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `character` WHERE `character_id` = '"+id+"'");

			while (rs.next()) {
				charList.add(mapChar(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return charList;
	}
	
	public static List<Char> getCharList(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Char> charList = new ArrayList<Char>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `character`");

			while (rs.next()) {
				charList.add(mapChar(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return charList;
	}
	
	public static List<Char> getCharListByLocationID(int location){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Char> charList = new ArrayList<Char>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `character` WHERE `location_id` = "+location);

			while (rs.next()) {
				charList.add(mapChar(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return charList;
	}

	public static List<Location> getLocation(String name){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Location> locationList = new ArrayList<Location>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `location` WHERE name = '"+name+"'");

			while (rs.next()) {
				locationList.add(mapLocation(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return locationList;
	}

	public static List<Location> getLocationByID(int id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Location> locationList = new ArrayList<Location>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `location` WHERE `location_id` = '"+id+"'");

			while (rs.next()) {
				locationList.add(mapLocation(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return locationList;
	}

	public static List<Location> getLocationList(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Location> locationList = new ArrayList<Location>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `location`");

			while (rs.next()) {
				locationList.add(mapLocation(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return locationList;
	}

	public static List<Item> getItem(String name){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Item> itemList = new ArrayList<Item>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `item` WHERE name = '"+name+"'");

			while (rs.next()) {
				itemList.add(mapItem(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return itemList;
	}
	
	public static List<Item> getItemByID(int id){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Item> itemList = new ArrayList<Item>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `item` WHERE `item_id` = '"+id+"'");

			while (rs.next()) {
				itemList.add(mapItem(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return itemList;
	}
	
	public static List<Item> getItemList(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Item> itemList = new ArrayList<Item>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `item`");

			while (rs.next()) {
				itemList.add(mapItem(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return itemList;
	}
	
	public static List<Item> getItemListByLocationID(int location){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Item> itemList = new ArrayList<Item>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `item` WHERE `location_id` ="+location);

			while (rs.next()) {
				itemList.add(mapItem(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return itemList;
	}
	
	public static List<Item> getItemListByCharacterID(int character){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Item> itemList = new ArrayList<Item>();

		try{
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS);  
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM `item` WHERE `character_id` ="+character);

			while (rs.next()) {
				itemList.add(mapItem(rs));
			}
		}

		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
		return itemList;
	}



	//hier kommen die Mapper $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

	private static Char mapChar(ResultSet rs) throws SQLException{
		Char chr = new Char();
		chr.setID(rs.getInt("character_id"));
		chr.setName(rs.getString("name"));
		chr.setType(rs.getString("typ"));
		chr.setLocation(rs.getInt("location_id"));
		return chr;
	}

	private static Location mapLocation(ResultSet rs) throws SQLException{
		Location loc = new Location();
		loc.setID(rs.getInt("location_id"));
		loc.setName(rs.getString("name"));
		loc.setType(rs.getString("typ"));
		loc.setX(rs.getInt("xcoord"));
		loc.setY(rs.getInt("ycoord"));
		return loc;
	}

	private static Item mapItem(ResultSet rs) throws SQLException{
		Item item = new Item();
		item.setID(rs.getInt("character_id"));
		item.setName(rs.getString("name"));
		item.setType(rs.getString("typ"));
		item.setCharacter(rs.getInt("character_id"));
		item.setLocation(rs.getInt("location_id"));
		item.setDescription(rs.getString("desc"));
		return item;
	}
	//hier kommen die anderen Befehle $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

	public static void sqlCommand(String befehl){
		Connection conn = null;
		Statement stmt = null;
		String sql = befehl;

		try{

			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection(DB_URL,USER,PASS); 
			stmt = conn.createStatement();
			stmt.executeUpdate(sql); 

		}
		catch (ClassNotFoundException e) {e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}

		finally {
			if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
	}

	@SuppressWarnings("null")
	public static void sqlBatch(List<String> makro){
		Connection conn = null;
		PreparedStatement prepStmt = null;

		try{

			conn.setAutoCommit(false);

			for(int i=0; i<makro.size();i++){
				prepStmt.addBatch(makro.get(i));
			}

			@SuppressWarnings("unused")
			int[] count = prepStmt.executeBatch();

			conn.commit();
		}

		catch(SQLException e){e.printStackTrace();}

		finally {
			if (prepStmt != null) try { prepStmt.close(); } catch (SQLException logOrIgnore) {}
			if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
		}
	}

}
